##! /bin/bash
#! bash
#
#  $Id: to_paragraph.sh 3 2010-05-27 16:25:49Z alistair.mills@btinternet.com $
#

### Main script stars here ###
# Store file name  here
export       FILE=$1
export      total=0
. ./defines.sh
. ./keys.sh

export   tmp_file_family=/tmp/file_for_alistair_
export               sql=sql
export               csv=csv
# echo $INPUT $OUTPUT
export  sql_file=$2
export  csv_file=$3

[ -f $sql_file ] && rm $sql_file
[ -f $csv_file ] && rm $csv_file
rm -rf $tmp_file_family*

# exit

     export number=0
     export file=1
     export k=0
     export tmp_file=${tmp_file_family}_${file}.txt
     export articleID=1


export p=0
while read year mon cat size url key trans line
do
  if [ "$line" != "$title_line" -a "$year" != "$comment_line" ]
  then
    for LANGUAGE in english french
    do
                                    export INPUT=${year}_${mon}_${cat}.txt
      [ "$LANGUAGE" = "french" ] && export INPUT=${year}_${mon}_${cat}_fr.txt
      if [ "$line" != ""  -a -f "$INPUT" -a "$LANGUAGE" = "english" ]
      then
        export       total=`expr $total + 1`
        export   articleID=$total
        export         tot=`printf '%.3i' $total`
        echo `date` Found $key: $tot - $INPUT - $line
      fi
      export     content=""
      export      number=1
      export change_date=`cat $last$INPUT`
      export start="insert into \`tblParagraph\` (\`articleID\` , \`number\`, \`language\`,  \`content\` ) \
                                           values ($articleID,     $number,  \"$LANGUAGE\", \""
      export start2="$articleID;$number;$LANGUAGE;\""
      while read content
      do
        k=`expr $k + 1`
        if [ "$content" != "" ]
        then
          export number=`expr $number + 1`
          echo $content >> $tmp_file
        else
          # echo file=$file tmp_file=$tmp_file number=$number wc=`wc -l $tmp_file`
          export file=`expr $file + 1`
          export tmp_file=${tmp_file_family}_$file.txt
        fi
      done < $INPUT

      k=0
      while [ $k -lt $file ]
      do
        export k=`expr $k + 1`
        export tmp_file=${tmp_file_family}_$k.txt
        if [ -f "$tmp_file" ]
        then
          export lines=`cat $tmp_file | wc -l`
          export n=0
          # echo reading file $tmp_file , $file
          export start_csv='"'
          export start_sql="insert into \`tblParagraph\` (\`articleID\` , \`number\`,  \`content\` ) values ( "
          export end_csv='"'
          export end_sql=');'

          while read content2
          do
            export n=`expr $n + 1`
            export content2="`echo $content2 | sed 's|"|\\\\"|g'`"
            if [ $lines -eq 1 ]
            then
              echo            ";${articleID};${k};${start_csv}${content2}${end_csv}"          >>  $csv_file
              echo "${start_sql}${articleID},${k},${start_csv}${content2}${end_csv}$end_sql"  >>  $sql_file
            fi

            if [ $lines -gt 1 -a $n -eq 1 ]
            then
              echo            ";${articleID};${k};${start_csv}${content2}"                    >>  $csv_file
              echo "${start_sql}${articleID},${k},${start_csv}${content2}"                    >>  $sql_file
            fi

            if [ $n -gt 1 -a $n -lt $lines ]
            then
              echo                                           "${content2}"                    >>  $csv_file
              echo                                           "${content2}"                    >>  $sql_file
            fi

            if [ $n -gt 1 -a $n -eq $lines ]
            then
              echo                                           "${content2}$end_csv"            >>  $csv_file
              echo                                           "${content2}${end_csv}$end_sql"  >>  $sql_file
            fi
          done < $tmp_file
        fi
      done
      rm -rf $tmp_file_family*
      export k=0
    done
  fi
done < $FILE
#
#
#
